package com.ai.view;

import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.ai.mmd.reviewed.vo.SbhzinfoVO;

/**
 * 导出特色航线Excel视图
 * 
 * @author Andy
 *
 */
public class BtTotalViewExcel extends AbstractExcelView {

	@SuppressWarnings("unchecked")
	@Override
	protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		
		String excelName = "武汉集装箱铁水联运专项补贴";
		Object sbnf = model.get("sbnf");
		Object sbjd = model.get("sbjd");
		String jdString = "";
		switch (Integer.parseInt(sbjd.toString())) {
		case 1:
			jdString = "一";
			break;
		case 2:
			jdString = "二";
			break;
		case 3:
			jdString = "三";
			break;
		case 4:
			jdString = "四";
			break;
		default:
			break;
		}
		
		if (sbnf!=null&&sbjd!=null) {
			excelName =  excelName + sbnf.toString() + "年第" + jdString + "季度汇总表";
		}
		
		// 设置response方式,使执行此controller时候自动出现下载页面,而非直接使用excel打开
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(excelName+".xls", "UTF-8"));

		// 产生Excel表头
		HSSFSheet sheetwh = workbook.createSheet(excelName);

		HSSFCellStyle titleStyle = workbook.createCellStyle();
//		titleStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
//		titleStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		titleStyle.setWrapText(true);

		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 18);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		titleStyle.setFont(font);
		
		//单元格样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框    
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setWrapText(true);
		HSSFFont cellfont = workbook.createFont();
		cellfont.setFontHeightInPoints((short) 18);
		cellStyle.setFont(cellfont);
		
		//foot样式
		HSSFCellStyle footerStyle = workbook.createCellStyle();
		footerStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		footerStyle.setVerticalAlignment(HSSFCellStyle.ALIGN_LEFT);
		footerStyle.setFont(cellfont);
		
		List<SbhzinfoVO> totallist = (List<SbhzinfoVO>) model.get("list");
		//获取企业名录
		int qyListSize = totallist.size();
		sheetwh.setColumnWidth(0,26*256);
		for(int i=1;i<=qyListSize;i++){
			sheetwh.setColumnWidth(i,26*256);
		}
		sheetwh.setColumnWidth(1+qyListSize,26*256);
		
		HSSFRow title = sheetwh.createRow(0);
		title.setHeight((short) 500);
		HSSFCell hsstitle = title.createCell(0);
		hsstitle.setCellValue(excelName);
		hsstitle.setCellStyle(titleStyle);
		sheetwh.addMergedRegion(new CellRangeAddress(0, 0, 0, 1+qyListSize));

		//第一行
		HSSFRow firstrow = sheetwh.createRow(1);
		firstrow.setHeightInPoints(80);
		//申请补贴单位
		HSSFCell r_xl = firstrow.createCell(0);
		r_xl.setCellValue("申请补贴单位");
		r_xl.setCellStyle(titleStyle);
		
		//企业
		for(int i=0;i<qyListSize;i++){
			HSSFCell r_qy = firstrow.createCell(i+1);
			HSSFRichTextString richqy = new HSSFRichTextString(totallist.get(i).getYhxxVO().getQyqc());
			r_qy.setCellValue(richqy);
			r_qy.setCellStyle(cellStyle);
		}
		
		//合计
		HSSFCell r_hj = firstrow.createCell(qyListSize+1);
		r_hj.setCellValue("合计");
		r_hj.setCellStyle(titleStyle);
		
		//第二行
		HSSFRow secondrow = sheetwh.createRow(2);
		secondrow.setHeightInPoints(80);
		//季度
		HSSFCell r_2_1 = secondrow.createCell(0);
		r_2_1.setCellValue("第"+ jdString +"季度        申请箱量          （单位：TEU）");
		r_2_1.setCellStyle(titleStyle);
		
		Integer zxltotal = 0;
		//企业
		for(int i=0;i<qyListSize;i++){
			HSSFCell r_qy = secondrow.createCell(i+1);
			HSSFRichTextString richqy = new HSSFRichTextString(totallist.get(i).getZxl().toString());
			r_qy.setCellValue(richqy);
			r_qy.setCellStyle(cellStyle);
			zxltotal += totallist.get(i).getZxl();
		}
		
		//合计
		HSSFCell r_2_n = secondrow.createCell(qyListSize+1);
		r_2_n.setCellValue(zxltotal.toString());
		r_2_n.setCellStyle(cellStyle);
		
		//第三行
		HSSFRow thirthrow = sheetwh.createRow(3);
		thirthrow.setHeightInPoints(80);
		//季度
		HSSFCell r_3_1 = thirthrow.createCell(0);
		r_3_1.setCellValue("申请补贴金额  （单位：万元）");
		r_3_1.setCellStyle(titleStyle);
		
		float cashtotal = 0;
		//企业
		for(int i=0;i<qyListSize;i++){
			HSSFCell r_qy = thirthrow.createCell(i+1);
			String cash = String.valueOf(Float.parseFloat(totallist.get(i).getBtje().toString())/10000);
			HSSFRichTextString richcrmtsj = new HSSFRichTextString(cash);;
			r_qy.setCellValue(richcrmtsj);
			r_qy.setCellStyle(cellStyle);
			cashtotal += Float.parseFloat(totallist.get(i).getBtje().toString())/10000;
		}
		DecimalFormat  fnum  =  new  DecimalFormat("##0.00");  
		String  cash = fnum.format(cashtotal); 
		
		//合计
		HSSFCell r_1_n = thirthrow.createCell(qyListSize+1);
		r_1_n.setCellValue(cash);
		r_1_n.setCellStyle(titleStyle);
		
		//第四行
		HSSFRow forthrow = sheetwh.createRow(4);
		forthrow.setHeightInPoints(80);
		//季度
		HSSFCell r_4_1 = forthrow.createCell(0);
		r_4_1.setCellValue("第"+ jdString +"季度申请   箱量合计      （单位：TEU）");
		r_4_1.setCellStyle(titleStyle);
		
		//该季度总箱量
		HSSFCell r_4_2 = forthrow.createCell(1);
		//HSSFCell r_4_3 = forthrow.createCell(2);
		r_4_2.setCellStyle(cellStyle);
		//r_4_3.setCellStyle(cellStyle);
		r_4_2.setCellValue(zxltotal.toString());
		//sheetwh.addMergedRegion(new CellRangeAddress(4, 4, 1, 2));
		
		//季度
		HSSFCell r_4_6 = forthrow.createCell(qyListSize);
		r_4_6.setCellValue("第"+ jdString +"季度申请补贴 金额合计          （单位：万元）");
		r_4_6.setCellStyle(titleStyle);
		
		//该季度总金额
		HSSFCell r_4_4 = forthrow.createCell(qyListSize+1);
		//HSSFCell r_4_5 = forthrow.createCell(5);
		r_4_4.setCellStyle(cellStyle);
		//r_4_5.setCellStyle(cellStyle);
		r_4_4.setCellValue(cash);
		//sheetwh.addMergedRegion(new CellRangeAddress(5, 5, 4, 5));
		
		//第五行
		HSSFRow fifthrow = sheetwh.createRow(5);
		fifthrow.setHeightInPoints(80);
		HSSFCell r_5_4 = fifthrow.createCell(qyListSize);
		r_5_4.setCellValue("制表：");
		r_5_4.setCellStyle(footerStyle);
		HSSFCell r_5_5 = fifthrow.createCell(qyListSize+1);
		r_5_5.setCellValue("复核：");
		r_5_5.setCellStyle(footerStyle);
	}
}
